package clothing.database;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.sql.Connection;

public class EmployeeManager {
	dbase db;

	public EmployeeManager() {
		db = new dbase();
	}
	
	public int authenticate(String username, String password)
	{
		String query = "Select count(*) from employees where username = '"+username+"' AND password ='"+password+"'";
		
		this.db.open();
		
		try {
			Statement s = this.db.getConn().createStatement();
			ResultSet rs = s.executeQuery(query);
			
			rs.next();
  			int count = rs.getInt(1);
			System.out.println(count);
			
			return count;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return 0;
		
	}

	public void insert(String firstname, String mi, String lastname,
			String phone, String address,  String username,
			String password, String type) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://199.17.162.76/Clothing", "root", "");

			System.out.println("Connected");
			//Statement st = conn.createStatement();
			
			/*
			String query = "insert into employees(firstname, mi, lastname, phone, address" +
					", username, password, Emp_Man) values('" + firstname
					+ "','" + mi + "','" + lastname + "','" + phone + "','"
					+ address + "','"  + username + "','"
					+ password + "','" +type + "')";
			System.out.println(query);*/
			
    		PreparedStatement p = conn.prepareStatement("insert into employees values('',?,?,?,?,?,?,?,?)");
			p.setString(1, firstname);
			p.setString(2, mi);
			p.setString(3, lastname);
			p.setString(4, phone);
			p.setString(5, address);			
			p.setString(6, username);
			p.setString(7, password);
			p.setString(8, type);
			
			
			p.executeUpdate();
			 
			//Statement s = conn.createStatement();
			//s.execute(query);
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void insertUser(String firstname, String mi, String lastname,
			String username, String password1, String address, String phone, String type) {
		this.db.open();
		try {
			String query = "INSERT INTO employees values('" + firstname + "','"
					+ mi + "','" + lastname + "','" + phone + "','" + address
					+ "','" + username + "','" + password1 + "','" + type +")";

			/*
			 * String query ="INSERT INTO users(firstname, mi, lastname, phone,
			 * address,username, password) values('"+firstname+"','" + mi +"','"
			 * + lastname +"','" + phone +"','" + address +"','" +
			 * username+"','"+password1 +"'"+ ")";
			 */
			System.out.println(query);

			Statement s = this.db.getConn().createStatement();
			s.execute(query);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public ArrayList<Employee> getList() {
		ArrayList<Employee> list = new ArrayList();

		this.db.open();
		try {
			Statement s = this.db.getConn().createStatement();
			String query = "SELECT * from employees";

			ResultSet rs = s.executeQuery(query);

			while (rs.next()) {

				int id = rs.getInt("uid");
				String firstname = rs.getString("firstname");
				String mi = rs.getString("mi");
				String lastname = rs.getString("lastname");
				String phone = rs.getString("phone");
				String address = rs.getString("address");
				String type = rs.getString("Emp_Man");
				String username = rs.getString("username");
				String password = rs.getString("password");

				Employee u = new Employee(id, firstname, mi, lastname, phone, address,
						 username, password, type);

				list.add(u);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
	
	public void remove(String uid)
	{
		this.db.open();
		Statement s;
		try {
			s = this.db.conn.createStatement();

			String query ="Delete from employees where uid = '"+uid+"'";
			
			s.execute(query);
			
			System.out.println(uid + "Deleted Successfully");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public Employee getEmployee(int id)
	{
		int iid = 0;
		String firstname = "";
		String mi = "";
		String lastname = ""; 
		String phone = "";
		String address = "";
		String username = "";
		String password = "";
		String type = "";
	
		this.db.open();
		Statement s;
		try {
			s = this.db.conn.createStatement();

			String query ="Select * from employees where uid = '"+id+"'";
			
			
			ResultSet rs = s.executeQuery(query);
			while (rs.next()) 
			{
		    iid = rs.getInt("uid");
			firstname = rs.getString("firstname");
			mi = rs.getString("mi");
			lastname = rs.getString("lastname");
			phone = rs.getString("phone");
			address = rs.getString("address");
			username = rs.getString("username");
			password = rs.getString("password");
			type = rs.getString("Emp_Man");
			}				

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		Employee e = new Employee(iid, firstname, mi, lastname, 
				phone, address, username, password, type);
		return e;
	}
	

	public void updateEmployee(int iid, String firstname, String mi, String lastname, 
			String phone, String address, String username, String password, String type)
    {
    	this.db.open();
		try {
			Statement s = this.db.getConn().createStatement();
			String query = "UPDATE employees SET firstname= '"+firstname+"', mi = '"+mi+"', " +
					"lastname = '"+lastname+"', phone = '"+phone+"', address = '"+address+"', " +
					"username= '"+username+"', password= '" + password+"', Emp_Man='"+ type+"'" +
							"Where uid='"+iid+"'";
					
			s.execute(query);
    }catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
    }
}
